package br.com.amadeus.android.dao;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import br.com.amadeus.android.entities.Course;
import br.com.amadeus.android.entities.CourseUser;
import br.com.amadeus.android.entities.Goal;
import br.com.amadeus.android.entities.User;

public class CourseUserDAO {
	public static String[] columns = new String[] { CourseUserDAO.ID,
			CourseUserDAO.COURSE_ID, CourseUserDAO.USER_ID };

	public static final String ID = "_id";
	public static final String COURSE_ID = "course_id";
	public static final String USER_ID = "user_id";

	private SQLiteDatabase db;

	private static CourseUserDAO instance = new CourseUserDAO();

	public static CourseUserDAO getInstance(Context ctx) {

		if (instance.db == null || !instance.db.isOpen()) {
			instance.db = new DBHelper(ctx).getWritableDatabase();
		}
		return instance;
	}

	private CourseUserDAO() {
	}

	public long save(CourseUser courseUser) {
		long id = courseUser.getId();
		if (id == 0) {
			return insert(courseUser);
		} else {
			return update(courseUser);
		}
	}

	private long insert(CourseUser courseUser) {
		long id_courseUser = -1;

		db.beginTransaction();
		try {

			ContentValues values = new ContentValues();
			values.put(CourseUserDAO.COURSE_ID, courseUser.getCourse().getId());
			values.put(CourseUserDAO.USER_ID, courseUser.getUser().getId());

			id_courseUser = db.insert(DBHelper.TABLE_COURSEUSER, null, values);

			if (id_courseUser != -1)
				db.setTransactionSuccessful();

		} finally {
			db.endTransaction();
		}

		return id_courseUser;
	}

	private long update(CourseUser courseUser) {
		boolean deuErro = false;
		db.beginTransaction();
		try {
			ContentValues values = new ContentValues();
			values.put(CourseUserDAO.COURSE_ID, courseUser.getCourse().getId());
			values.put(CourseUserDAO.USER_ID, courseUser.getUser().getId());

			deuErro = db.update(DBHelper.TABLE_COURSEUSER, values,
					CourseUserDAO.ID + " = ?",
					new String[] { String.valueOf(courseUser.getId()) }) == 0;

			if (!deuErro)
				db.setTransactionSuccessful();

		} finally {
			db.endTransaction();
		}
		return courseUser.getId();
	}

	public void delete(int id) {
		db.beginTransaction();
		try {
			db.delete(DBHelper.TABLE_COURSEUSER, CourseUserDAO.ID + "=?",
					new String[] { String.valueOf(id) });
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
	}
	
	public List<CourseUser> getListCourseUserByCourse(int course_id){
		
		List<CourseUser> courseUserList = new ArrayList<CourseUser>();
		Cursor c = db.query(DBHelper.TABLE_COURSEUSER, columns, CourseUserDAO.COURSE_ID + "=" + course_id, null, null, null, null);
		if (c.moveToFirst()) {
			int id_id = c.getColumnIndex(CourseUserDAO.ID);
			int id_course_id = c.getColumnIndex(CourseUserDAO.COURSE_ID);
			int id_user_id = c.getColumnIndex(CourseUserDAO.USER_ID);
			
			do {
				int id = c.getInt(id_id);
				int course_id2 = c.getInt(id_course_id);
				int user_id = c.getInt(id_user_id);
				
				CourseUser courseUser = new CourseUser(id, new Course(course_id2), new User(user_id));
				courseUserList.add(courseUser);
				
			} while (c.moveToNext());
		}
		c.close();
		return courseUserList;
	}
		
	public ArrayList<CourseUser> getListCourseUser(){
		ArrayList<CourseUser> courseUserList = new ArrayList<CourseUser>();
		Cursor c = db.query(DBHelper.TABLE_COURSEUSER, columns, null, null, null, null, null);
		if (c.moveToFirst()) {
			int id_id = c.getColumnIndex(CourseUserDAO.ID);
			int id_course_id = c.getColumnIndex(CourseUserDAO.COURSE_ID);
			int id_user_id = c.getColumnIndex(CourseUserDAO.USER_ID);
			
			do {
				int id = c.getInt(id_id);
				int course_id = c.getInt(id_course_id);
				int user_id = c.getInt(id_user_id);
				
				CourseUser courseUser = new CourseUser(id, new Course(course_id), new User(user_id));
				courseUserList.add(courseUser);
				
			} while (c.moveToNext());
		}
		c.close();
		return courseUserList;
	}

	public CourseUser getCourseUser(int id) {
		CourseUser cu = null;
		Cursor c = db.query(DBHelper.TABLE_COURSEUSER, null, CourseUserDAO.ID
				+ "=" + id, null, null, null, null);
		if (c.getCount() > 0) {
			c.moveToFirst();
			cu = fillCourseUser(c);
		}
		c.close();
		return cu;
	}

	protected static CourseUser fillCourseUser(Cursor c) {
		int id = c.getInt(c.getColumnIndex(CourseUserDAO.ID));
		Course course = new Course(c.getInt(c
				.getColumnIndex(CourseUserDAO.COURSE_ID)));
		User user = new User(c.getInt(c.getColumnIndex(CourseUserDAO.USER_ID)));

		return new CourseUser(id, course, user);
	}

	public CourseUser getCourseUserWithCourseAndUser(int id) {
		Cursor c = db.query(DBHelper.TABLE_COURSEUSER, null, CourseUserDAO.ID
				+ "=" + id, null, null, null, null);
		if (c.getCount() > 0) {
			c.moveToFirst();
			return fillCourseUser(c);
		}
		return null;
	}

	protected CourseUser fillCourseUserWithCourseAndUser(Cursor c) {
		int id = c.getInt(c.getColumnIndex(CourseUserDAO.ID));
		Course course = new Course(c.getInt(c
				.getColumnIndex(CourseUserDAO.COURSE_ID)));
		User user = new User(c.getInt(c.getColumnIndex(CourseUserDAO.USER_ID)));

		Cursor cc = db.query(DBHelper.TABLE_COURSEUSER, null, "course_id = ?",
				new String[] { String.valueOf(course.getId()) }, null, null,
				null);
		if (cc.getCount() > 0) {
			cc.moveToFirst();
			course = CourseDAO.fillCourse(cc);
		}

		Cursor cu = db
				.query(DBHelper.TABLE_USER, null, "user_id = ?",
						new String[] { String.valueOf(user.getId()) }, null,
						null, null);
		if (cu.getCount() > 0) {
			cu.moveToFirst();
			user = UserDAO.fillUser(cu);
		}

		return new CourseUser(id, course, user);
	}

	public CourseUser getCourseUserFull(int id) {
		Cursor c = db.query(DBHelper.TABLE_COURSEUSER, null, CourseUserDAO.ID
				+ "=" + id, null, null, null, null);
		if (c.getCount() > 0) {
			c.moveToFirst();
			return fillCourseUserFull(c);
		}
		return null;
	}

	protected CourseUser fillCourseUserFull(Cursor c) {
		int id = c.getInt(c.getColumnIndex(CourseUserDAO.ID));
		Course course = new Course(c.getInt(c
				.getColumnIndex(CourseUserDAO.COURSE_ID)));
		User user = new User(c.getInt(c.getColumnIndex(CourseUserDAO.USER_ID)));

		Cursor cc = db.query(DBHelper.TABLE_COURSE, null, "course_id = ?",
				new String[] { String.valueOf(course.getId()) }, null, null,
				null);
		if (cc.getCount() > 0) {
			cc.moveToFirst();
			course = CourseDAO.fillCourse(cc);
		}

		Cursor cu = db
				.query(DBHelper.TABLE_USER, null, "user_id = ?",
						new String[] { String.valueOf(user.getId()) }, null,
						null, null);
		if (cu.getCount() > 0) {
			cu.moveToFirst();
			user = UserDAO.fillUser(cu);
		}
		CourseUser courseUser = new CourseUser(id, course, user, new ArrayList<Goal>());

		Cursor gc = db.query(DBHelper.TABLE_GOAL, null, "course_user_id = ?",
				new String[] { String.valueOf(courseUser.getId()) }, null,
				null, null);

		gc.moveToFirst();

		while (!gc.isAfterLast()) {
			courseUser.getGoalList().add(GoalDAO.fillGoal(gc));
			gc.moveToNext();
		}

		return courseUser;
	}

	public int getUser() {
		// TODO Auto-generated method stub
		Cursor c = db.query(DBHelper.TABLE_COURSEUSER, columns, null, null, null, null, null);
		c.moveToFirst();
		return c.getInt(c.getColumnIndex(CourseUserDAO.USER_ID));
	}

	public int getCourseUserByCourse(int course_id) {
		// TODO Auto-generated method stub
		Cursor c = db.query(DBHelper.TABLE_COURSEUSER, columns, "course_id = "+course_id ,null, null, null, null);
		c.moveToFirst();
		int id = c.getInt(c.getColumnIndex(CourseUserDAO.ID));
		c.close();
		return id;
	}
	
	public int getCourseByCourseUser(int course_user_id){
		Cursor c = db.query(DBHelper.TABLE_COURSEUSER, columns, CourseUserDAO.ID + "=" + course_user_id ,null, null, null, null);
		c.moveToFirst();
		int amadeus_id = c.getInt(c.getColumnIndex(CourseUserDAO.COURSE_ID));
		c.close();
		return amadeus_id;
	}
}
